<?php

class Profile_model extends CI_Model {
    
    public function get_profile($offset,$num,$order='id desc',$userid,$condition,$type){
        $table   = "lfsrlzy_profile";
        $str_sql  = "select * from {$table} where ";
        if($type == 4){
            $str_sql .= "type = 4 ";
            $this->db->where("type",4);
        }else if($type == 5){
            $str_sql .= "type = 5 ";
            $this->db->where("type",5);
        }else{
            $str_sql .= "type != 4 and type != 5 ";
            $this->db->where("type !=",4);
            $this->db->where("type !=",5);
        }
        $str_sql .= "and name like '%".$condition."%' ";
        $str_sql .=" order by {$order} limit {$offset},{$num}";
        $this->db->like('name', $condition);
        $this->db->from('profile');
        return array(
            'total' => $this->db->count_all_results(),
            'res' => $this->db->query($str_sql)->result_array(),
        ); 
    }
    
    public function get_order($offset,$num,$order='id desc',$condition,$type){
        $str_sql  = "select o.*,c.name as companyname,e.username as salesman from lfsrlzy_order o left join lfsrlzy_company c "
        . "on c.id = o.companyid "
        . "left join lfsrlzy_user e on e.id = o.saleser "
        . "where o.type = {$type} and "
        . "(o.sn like '%".$condition."%' or o.position like '%".$condition."%' or c.name like '%".$condition."%' or e.username like '%".$condition."%')"
        . " order by {$order} limit {$offset},{$num}";
        $sql = "select o.*,c.name as companyname,e.username as salesman from lfsrlzy_order o left join lfsrlzy_company c "
        . "on c.id = o.companyid "
        . "left join lfsrlzy_user e on e.id = o.saleser "
        . "where o.type = {$type} and (o.sn like '%".$condition."%' or o.position like '%".$condition."%' or c.name like '%".$condition."%' or e.username like '%".$condition."%')";
        return array(
            'total' => $this->db->query($sql)->num_rows(),
            'res' => $this->db->query($str_sql)->result_array(),
        ); 
    }
    
    public function neworder(){
        $data['sn'] = $this->input->post('sn');
        $data['position'] = $this->input->post('position');
        $data['companyid'] = $this->input->post('companyid');
        $data['salary'] = $this->input->post('salary');
        $data['saleser'] = $this->input->post('saleser');
        $data['priority'] = $this->input->post('priority');
        $data['worktype'] = $this->input->post('worktype');
        $data['starttime'] = $this->input->post('s_time');
        $data['endtime'] = $this->input->post('e_time');
        $data['gender'] = $this->input->post('gender');
        $data['workintro'] = $this->input->post('workintro');
        $data['comment'] = $this->input->post('comment');
        $data['employeeid'] = $this->session->userdata('user')->id;
        $data['createtime'] = time();
        $data['updatetime'] = $data['createtime'];
        $this->db->insert('order', $data);
        return $this->db->insert_id();
    }
    
    public function getAllsn(){
        return $this->db->get("order")->result_array();
    }
    
    public function deleteorder(){
        $id = $this->input->get("id");
        $this->db->delete('order', array('id' => $id));
    }
    
    public function updateorder(){
        $data['sn'] = $this->input->post('sn');
        $data['position'] = $this->input->post('position');
        $data['companyid'] = $this->input->post('companyid');
        $data['salary'] = $this->input->post('salary');
        $data['saleser'] = $this->input->post('saleser');
        $data['priority'] = $this->input->post('priority');
        $data['worktype'] = $this->input->post('worktype');
        $data['starttime'] = $this->input->post('s_time');
        $data['endtime'] = $this->input->post('e_time');
        $data['gender'] = $this->input->post('gender');
        $data['workintro'] = $this->input->post('workintro');
        $data['comment'] = $this->input->post('comment');
        $data['updatetime'] = time();
        $this->db->update('order', $data, array('id' => $this->input->post('id')));
    }
    
    public function getOrderByid($id){
        $sql = "select o.*,c.name as companyname,u.username as employeename,c.id as companyid from lfsrlzy_order o "
                . "left join lfsrlzy_company c on c.id = o.companyid "
                . "left join lfsrlzy_user u on u.id = o.employeeid "
                . "where o.id = ".$id;
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    
    public function getOrderBySN($sn){
        $sql = "select o.*,c.name as companyname from lfsrlzy_order o left join lfsrlzy_company c "
                . "on c.id = o.companyid where o.sn = '".$sn."'";
        $query = $this->db->query($sql);
        return $query->row_array();
    }
    
    public function create($tablename,$data){
        $this->db->insert($tablename, $data);
        return $this->db->insert_id();
    }
    
    public function getProfileByID($id){
        $query = $this->db->get_where('profile', array('id' => $id));
        return $query->row_array();
    }
    
    public function My_Delete($tablename,$id){
        $this->db->delete($tablename, array('id' => $id));
    }
    
    public function getUseableOrder($condition){
        $sql = "select o.id,o.sn,o.position,o.salary,o.worktype,o.gender,c.name from lfsrlzy_order o "
                . "left join lfsrlzy_company c on c.id = o.companyid where (o.type = 0 or o.type = 3) "
                . "and (o.sn like '%".$condition."%' or c.name like '%".$condition."%' or o.position like '%".
                $condition."%' or o.salary like '%".$condition."%') and "
                . "o.id in (select w.ordersn from lfsrlzy_workers w where w.ordersn != 0 and w.ordersn is not null and w.ordersn != '')";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    
    public function getOrderByType($type=0,$condition=''){
        $sql = "select o.*,c.name,c.id as companyid from lfsrlzy_order o "
                . "left join lfsrlzy_company c on c.id = o.companyid "
                . "where o.type = ".$type;
        if($condition != '' && $condition != NULL){
            $sql .= " and (o.sn like '%".$condition."%' or o.position like '%".$condition.
                "%' or c.name like '%".$condition."%')";
        }
        $query = $this->db->query($sql);
        return $query->result_array();
    }
    
    /**
     * 根据传入条件查询没有归档的profile
     * @param type $condition 查询条件
     */
    public function getProfileByCondition($condition){
        $sql = "select * from lfsrlzy_profile where name like '%{$condition}%' and type != 3";
        $query = $this->db->query($sql);
        return $query->result_array();
    }
}

?>
